IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAdvanceFundRequestApprovalsByProposalNumber]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetAdvanceFundRequestApprovalsByProposalNumber]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--GetAdvanceFundRequestApprovalsByProposalNumber '2009A050008'

CREATE PROCEDURE [dbo].[GetAdvanceFundRequestApprovalsByProposalNumber] 
(  
	@PropNumber	VARCHAR(50)
)  
AS 

SET NOCOUNT ON
BEGIN  
		    
			SELECT  
			CASE afst.Id WHEN '3' THEN 'PI Signoff' WHEN '4' THEN 'Department Signoff' WHEN '5' THEN 'GA Signoff' WHEN '6' THEN 'Denied' END AS 'Status',
			adm.LastName + ', ' + adm.FirstName AS 'ApprovedBy',
			CONVERT(VARCHAR(10), awf.DateStart,101) AS 'ApprovedDate'
			FROM dbo.AdvanceFundRequestWorkFlowStatus  AS awf	
			INNER JOIN 	dbo.AdvanceFundRequest AS afr ON awf.AdvanceFundRequestId = afr.Id
			INNER JOIN  dbo.AdvanceFundRequestStatusType AS afst ON awf.AdvanceFundRequestStatusTypeId = afst.Id
			INNER JOIN dbo.AdmPerson AS adm ON adm.FwkDomainUserId = awf.CreatedBy 
			WHERE afr.PropNumber = @PropNumber
			AND afst.Id > 2
			ORDER BY awf.DateStart

END




GO